package pl.edu.agh.io.sylvia.dao;

import java.io.StringReader;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;


import pl.edu.agh.io.sylvia.entity.Article;
import pl.edu.agh.io.sylvia.entity.Monitor;


public class ArticleDAO extends GenericDAO {

	@Override
	public List<Article> getAll() {
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<Article> results= new ArrayList<Article>();

		try {
			statement = connection.prepareStatement("SELECT * FROM ARTICLES");
			resultSet = statement.executeQuery();

			GroupDAO groupDAO = new GroupDAO();
			MonitorDAO monitorDAO = new MonitorDAO();

			while(resultSet.next()) {
				int id = resultSet.getInt("id");
				int groupId = resultSet.getInt("groups_id");
				String header = resultSet.getString("header");
				String body = resultSet.getString("body");
				results.add(new Article(id, header, body, groupDAO.getById(groupId), monitorDAO.getByArticleId(id))); 
			}
			return results;
		} catch (SQLException e) {
			log.log(Level.SEVERE, e.getMessage(), e);
			return null;
		} finally {
			if (resultSet != null) try {
				resultSet.close();
			} catch(SQLException e) {};
			if (statement != null) try {
				statement.close();
			} catch (SQLException e) {};
		}
	}

	public Article getByMessageId(String id) {
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			statement = connection.prepareStatement("SELECT * FROM ARTICLES WHERE header like ?");
			statement.setString(1, "%Message-ID: " + id + "%\n");
			resultSet = statement.executeQuery();

			GroupDAO groupDAO = new GroupDAO();
			MonitorDAO monitorDAO = new MonitorDAO();

			if (resultSet.next()) {
				int intId = resultSet.getInt("id");
				String header = resultSet.getClob("header").toString();
				String body = resultSet.getClob("body").toString();
				int groupId = resultSet.getInt("groups_id");
				return new Article(intId, header, body, groupDAO.getById(groupId), monitorDAO.getByArticleId(intId));
			} else {
				return null;
			}
		} catch (SQLException e) {
			log.log(Level.SEVERE, e.getMessage(), e);
			e.printStackTrace();
			return null;
		} finally {
			if (resultSet != null) try {
				resultSet.close();
			} catch(SQLException e) {};
			if (statement != null) try {
				statement.close();
			} catch (SQLException e) {};
		}
	}

	@Override
	public Article getById(int id) {
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {
			statement = connection.prepareStatement("SELECT * FROM ARTICLES WHERE id = ?");
			statement.setInt(1, id);
			resultSet = statement.executeQuery();

			GroupDAO groupDAO = new GroupDAO();
			MonitorDAO monitorDAO = new MonitorDAO();

			if (resultSet.next()) {
				String header = resultSet.getClob("header").toString();
				String body = resultSet.getClob("body").toString();
				int groupId = resultSet.getInt("groups_id");
				return new Article(id, header, body, groupDAO.getById(groupId), monitorDAO.getByArticleId(id));
			} else {
				return null;
			}
		} catch (SQLException e) {
			log.log(Level.SEVERE, e.getMessage(), e);
			e.printStackTrace();
			return null;
		} finally {
			if (resultSet != null) try {
				resultSet.close();
			} catch(SQLException e) {};
			if (statement != null) try {
				statement.close();
			} catch (SQLException e) {};
		}
	}

	public void makePersistent(Article article) {
		if (article.getId() == null) {

			PreparedStatement statement = null;
			ResultSet resultSet = null;
			int lastInsertId = 0;

			try {
				statement = connection.prepareStatement("INSERT INTO ARTICLES VALUES(null, ?, ?, ?)");
				statement.setClob(1, new StringReader(article.getHeader()));
				statement.setClob(2, new StringReader(article.getBody()));
				statement.setInt(3, article.getGroup().getId());
				statement.execute();
				statement.close();
				statement = null;
				statement = connection.prepareStatement("SELECT LAST_INSERT_ID()");
				resultSet = statement.executeQuery();
				lastInsertId = 0;
				if (resultSet.next()) {
					lastInsertId = resultSet.getInt(1);
					article = new Article(lastInsertId, article.getHeader(), article.getBody(), article.getGroup(), article.getMonitors());
				}
				statement.close();
				resultSet.close();
				resultSet = null;
			} catch (Throwable e) {
				// DO NOTHING
			} finally {
				if (resultSet != null) try {
					resultSet.close();
				} catch(SQLException e) {};
				if (statement != null) try {
					statement.close();
				} catch (SQLException e) {};
			}
			
			try {
				statement = connection.prepareStatement("INSERT INTO MONITORS_DETAILS VALUES(?, ?)");
				statement.setInt(1, lastInsertId);
				
				List<Monitor> monitors = article.getMonitors();
				for (Monitor monitor : monitors) {
					int monitorId = monitor.getId();
					statement.setInt(2, monitorId);
					statement.execute();
				}
				statement.close();
				statement = null;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				if (resultSet != null) try {
					resultSet.close();
				} catch(SQLException e) {};
				if (statement != null) try {
					statement.close();
				} catch (SQLException e) {};
			}
		} else {
			//TODO: Implement
		}
	}
	
	public List<Article> getByMonitorID(int id) {
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<Article> results= new ArrayList<Article>();
		
		
		try {
			statement = connection.prepareStatement("SELECT * FROM ARTICLES JOIN MONITORS_DETAILS ON MONITORS_DETAILS.ARTICLES_ID = ARTICLES.ID WHERE MONITORS_DETAILS.MONITORS_ID = ?");
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			MonitorDAO monitorDAO = new MonitorDAO();
			GroupDAO groupDAO = new GroupDAO();
			
			while(resultSet.next()) {
				String header = resultSet.getClob("header").toString();
				String body = resultSet.getClob("body").toString();
				int groupId = resultSet.getInt("groups_id");				
				results.add( new Article(id, header, body, groupDAO.getById(groupId), monitorDAO.getByArticleId(id)));			 
			}
			return results;
		} catch (SQLException e) {
			log.log(Level.SEVERE, e.getMessage(), e);
			return null;
		} finally {
			if (resultSet != null) try {
				resultSet.close();
			} catch(SQLException e) {};
			if (statement != null) try {
				statement.close();
			} catch (SQLException e) {};
		}
	}
}

